Computerized spreadsheet applications, such as Microsoft Corporation's EXCEL software product, have gained widespread use. Individuals and businesses routinely use computerized spreadsheets for a variety of purposes, such as accounting, finance, and investments. Over the years, the functionality made available to users of these spreadsheet applications has grown significantly. Business applications often use spreadsheet applications to assist in interpreting large amounts of data. In many cases, the interpretation includes conducting calculations on the data to obtain meaningful results based on the particular business objective.
In large business applications that use spreadsheet applications to evaluate data, the spreadsheets may contain a great deal of duplication, either in the data or in the formulas used with the data. The time to create these large spreadsheets can be substantial. In addition, any effort to reproduce large spreadsheets by keying in the data into the cells of the spreadsheet has a high risk of keystroke errors being interspersed within the data with lithe or no chance to find the errors. In order to avoid this problem, most users select portions of the spreadsheet that they want to copy, be it data or formulas, and paste it to another spreadsheet or another portion of the current spreadsheet. These copy and paste procedures may include hundreds or even thousands of rows of cells.
Unfortunately, the constant duplication of calculations over time can create inconsistencies. Furthermore, when a problem in a calculation is detected, it is difficult to determine where and when the problem first arose. The only way to confirm that all of the data sets that include that calculation are correct is to manually go through each data set and confirm the formula. Even if the user is capable of manually reviewing each instance of the calculation, which may have been copied and pasted hundreds of times, there is still a high probability that either through keystroke error or fatigue during the review process, an error will remain in one or more of the calculations.
Another problem with cutting and pasting large portions of data that contain calculations is that this process is prone to both accidental and intentional manipulation of the calculations. Since most users who cut and paste the calculations will not take the time to independently verify the veracity of each of the calculations prior to duplication, a user can make revisions to one or more of the calculations in order to manipulate the output data. While the above example is described as intentionally occurring, the same problem can occur accidentally and will be just as problematic.
Yet another problem with large spreadsheet applications used in the business context, where users have cut and pasted large areas of calculations numerous times, is that governmental regulators have a dim view of spreadsheets and the data that is supposedly output by the spreadsheet applications because the regulators cannot reliably determine if one row of data has a different set of formulas from another row of data.
In view of the foregoing there is a need in the art for a method to allow a spreadsheet application user to capture a sequence of calculations as a function, store the function as a function definition in a worksheet of the spreadsheet application, and provide the user with the ability to call or select a code associated with the function in the spreadsheet application. Then when the user needs to verify or modify the form of the calculation, the user only needs to review the function definition and not each use of the function in the spreadsheet application.